const fs = require('fs-extra');
var sqlite3 = require('sqlite3').verbose();

var DB = DB || {};

DB.SqliteDB = function (file) {
    DB.db = new sqlite3.Database(file);

    DB.exist = fs.existsSync(file);
    if (!DB.exist) {
        fs.openSync(file, 'w');
    };
};

DB.printErrorInfo = function (err) {
    console.log("Error Message:", err);
};

DB.SqliteDB.prototype.createTable = function (sql, callback) {
    DB.db.serialize(function () {
        DB.db.run(sql, function (err) {
            if (null != err) {
                DB.printErrorInfo(err);
                return;
            }
            callback()
        });
    });
};

/// tilesData format; [[level, column, row, content], [level, column, row, content]]
DB.SqliteDB.prototype.insertData = function (sql, objects) {
    DB.db.serialize(function () {
        var stmt = DB.db.prepare(sql);
        for (var i = 0; i < objects.length; ++i) {
            stmt.run(objects[i]);
        }

        stmt.finalize();
    });
};

DB.SqliteDB.prototype.queryData = function (sql, callback) {
    DB.db.all(sql, function (err, rows) {
        if (null != err) {
            DB.printErrorInfo(err);
            return;
        }
        if (callback) {
            callback(rows)
        }
    });
};

DB.SqliteDB.prototype.executeSql = function (sql) {
    DB.db.run(sql, function (err) {
        if (null != err) {
            DB.printErrorInfo(err);
        }
    });
};

DB.SqliteDB.prototype.close = function () {
    DB.db.close();
};


const SqliteDB = DB.SqliteDB

module.exports = {
    SqliteDB
}




// /// Import SqliteDB.

// var SqliteDB = require('./sqlite.js').SqliteDB;



// var file = "Gis1.db";

// var sqliteDB = new SqliteDB(file);



// /// create table.

// var createTileTableSql = "create table if not exists tiles(level INTEGER, column INTEGER, row INTEGER, content BLOB);";

// var createLabelTableSql = "create table if not exists labels(level INTEGER, longitude REAL, latitude REAL, content BLOB);";

// sqliteDB.createTable(createTileTableSql);

// sqliteDB.createTable(createLabelTableSql);



// /// insert data.

// var tileData = [[1, 10, 10], [1, 11, 11], [1, 10, 9], [1, 11, 9]];

// var insertTileSql = "insert into tiles(level, column, row) values(?, ?, ?)";

// sqliteDB.insertData(insertTileSql, tileData);



// /// query data.

// var querySql = 'select * from tiles where level = 1 and column >= 10 and column <= 11 and row >= 10 and row <=11';

// sqliteDB.queryData(querySql, dataDeal);



// /// update data.

// var updateSql = 'update tiles set level = 2 where level = 1 and column = 10 and row = 10';

// sqliteDB.executeSql(updateSql);



// /// query data after update.

// querySql = "select * from tiles where level = 2";

// sqliteDB.queryData(querySql, dataDeal);



// sqliteDB.close();



// function dataDeal(objects){

//     for(var i = 0; i < objects.length; ++i){

//         console.log(objects[i]);

//     }

// }